/**
 * 诊断时间字段问题
 * 创建时间: 2025-01-27
 * 说明: 找出具体哪个表的createdAt字段还有问题
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

// 从环境变量获取数据库配置
const dbConfig = {
  host: process.env.DB_HOST || '43.143.253.188',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'nextjs',
  password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
  database: process.env.DB_NAME || 'nextjs',
  timezone: '+08:00'
};

async function diagnoseDatetimeIssue() {
  let connection;
  
  try {
    console.log('🔌 连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');
    
    // 1. 检查所有有问题的createdAt字段
    console.log('\n🔍 检查所有createdAt字段...');
    const [createdAtColumns] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        COLUMN_NAME,
        DATA_TYPE,
        DATETIME_PRECISION,
        COLUMN_DEFAULT,
        IS_NULLABLE,
        COLUMN_TYPE
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? 
        AND COLUMN_NAME IN ('createdAt', 'created_at')
        AND DATA_TYPE = 'datetime'
      ORDER BY TABLE_NAME
    `, [dbConfig.database]);
    
    console.log('发现的createdAt字段:');
    createdAtColumns.forEach(col => {
      console.log(`  表: ${col.TABLE_NAME}`);
      console.log(`    字段: ${col.COLUMN_NAME}`);
      console.log(`    类型: ${col.COLUMN_TYPE}`);
      console.log(`    精度: ${col.DATETIME_PRECISION || 'N/A'}`);
      console.log(`    默认值: ${col.COLUMN_DEFAULT}`);
      console.log(`    可空: ${col.IS_NULLABLE}`);
      console.log('');
    });
    
    // 2. 尝试修复有问题的字段
    console.log('\n🔧 尝试修复有问题的字段...');
    for (const col of createdAtColumns) {
      const precision = col.DATETIME_PRECISION || 0;
      if (precision !== 3) {
        console.log(`修复表 ${col.TABLE_NAME} 的 ${col.COLUMN_NAME} 字段...`);
        
        try {
          await connection.execute(`
            ALTER TABLE \`${col.TABLE_NAME}\` 
            MODIFY COLUMN \`${col.COLUMN_NAME}\` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间（精确到毫秒）'
          `);
          console.log(`  ✅ ${col.TABLE_NAME}.${col.COLUMN_NAME} 修复成功`);
        } catch (error) {
          console.log(`  ❌ ${col.TABLE_NAME}.${col.COLUMN_NAME} 修复失败: ${error.message}`);
          
          // 尝试回滚到兼容模式
          try {
            await connection.execute(`
              ALTER TABLE \`${col.TABLE_NAME}\` 
              MODIFY COLUMN \`${col.COLUMN_NAME}\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
            `);
            console.log(`    ✅ ${col.TABLE_NAME}.${col.COLUMN_NAME} 回滚到兼容模式成功`);
          } catch (rollbackError) {
            console.log(`    ❌ ${col.TABLE_NAME}.${col.COLUMN_NAME} 回滚失败: ${rollbackError.message}`);
          }
        }
      } else {
        console.log(`  ⏭️  ${col.TABLE_NAME}.${col.COLUMN_NAME} 已经是毫秒级精度，跳过`);
      }
    }
    
    // 3. 验证修复结果
    console.log('\n🔍 验证修复结果...');
    const [updatedColumns] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        COLUMN_NAME,
        DATA_TYPE,
        DATETIME_PRECISION,
        COLUMN_DEFAULT
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? 
        AND COLUMN_NAME IN ('createdAt', 'created_at')
        AND DATA_TYPE = 'datetime'
      ORDER BY TABLE_NAME
    `, [dbConfig.database]);
    
    console.log('修复后的createdAt字段状态:');
    updatedColumns.forEach(col => {
      console.log(`  ${col.TABLE_NAME}.${col.COLUMN_NAME}: ${col.DATA_TYPE}(${col.DATETIME_PRECISION || 'N/A'}) - 默认值: ${col.COLUMN_DEFAULT}`);
    });
    
    // 4. 测试数据库连接
    console.log('\n🧪 测试数据库连接...');
    try {
      const [testResult] = await connection.execute('SELECT 1 as test');
      console.log('✅ 数据库连接测试成功:', testResult[0]);
      
      // 尝试查询一个表
      const [tableTest] = await connection.execute('SHOW TABLES');
      console.log('✅ 表查询测试成功，找到', tableTest.length, '个表');
      
    } catch (testError) {
      console.log('❌ 数据库连接测试失败:', testError.message);
    }
    
    console.log('\n🎉 诊断完成！');
    
  } catch (error) {
    console.error('❌ 诊断失败:', error.message);
    console.error('错误详情:', error);
    
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 运行诊断
if (require.main === module) {
  diagnoseDatetimeIssue().catch(console.error);
}

module.exports = { diagnoseDatetimeIssue }; 